package com.aqumon.www.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.fastjson.JSON;
import com.aqumon.www.entity.persistent.RaaSTestAcc;
import com.aqumon.www.entity.request.data.*;
import com.aqumon.www.entity.response.controller.PageModule;
import com.aqumon.www.entity.response.data.RaaSAccListResp;
import com.aqumon.www.mapper.RaasTestAccMapper;
import com.aqumon.www.service.RaaSTestAccService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
//import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigInteger;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * <p>
 * raas 测试账号 服务实现类
 * </p>
 *
 * @author aqumon
 * @since 2022-06-02
 */
@Service
public class RaaSTestAccServiceImpl extends ServiceImpl<RaasTestAccMapper, RaaSTestAcc> implements RaaSTestAccService {

    private static final Logger LOGGER = LoggerFactory.getLogger(RaaSTestAccServiceImpl.class);

    @Resource
    private RaasTestAccMapper raasTestAccMapper;

    private void checkExcel(MultipartFile file) throws Exception {
        // 判断文件是否存在
        if (null == file) {
            throw new FileNotFoundException("文件不存在！");
        }
        // 获得文件名
        String fileName = file.getOriginalFilename();
        // 判断文件是否是excel文件
        if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
            throw new IOException(fileName + "不是excel文件");
        }
    }

    private Workbook getWorkBook(MultipartFile file) {
        // 获得文件名
        String fileName = file.getOriginalFilename();
        // 创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            // 获取excel文件的io流
            InputStream is = file.getInputStream();
            // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                // 2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                // 2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {

        }
        return workbook;
    }

    private String getCellStringVal(Cell cell) {
        CellType cellType = cell.getCellType();
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); //日期格式yyyy-mm-dd
        DecimalFormat  df = new DecimalFormat("0");             //数字格式，防止长数字成为科学计数法形式
        switch (cellType) {
            case NUMERIC:
                return df.format(cell.getNumericCellValue());
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case ERROR:
                return String.valueOf(cell.getErrorCellValue());
            default:
                return StringUtils.EMPTY;
        }
    }

    @Override
    public List<RaaSTestAcc> importAccountExcel(RaaSAccountImportReq req, HttpServletRequest request) throws Exception {

        checkExcel(req.getFile());
        List<RaaSTestAcc> raaSTestAccList = new ArrayList<>();

        Workbook wb = getWorkBook(req.getFile());
        Sheet sheet = wb.getSheetAt(0); // 此处默认获取第一页
        // 获取每行中的字段
        for (int j = 1; j <= sheet.getLastRowNum(); j++) { // 从第二行开始遍历
            Row row = sheet.getRow(j); // 获取行
            RaaSTestAcc raasTestAcc = new RaaSTestAcc();
            for (int col = 0; col < row.getLastCellNum(); col++) {
                Cell cell = row.getCell(col);
                String cellValue = getCellStringVal(cell) == null ? "" : getCellStringVal(cell).trim();
                switch (col) {
                    case 0: {
                        raasTestAcc.setProject(cellValue);
                        break;
                    }
                    case 1: {
                        raasTestAcc.setEnv(cellValue);
                        break;
                    }
                    case 2: {
                        raasTestAcc.setUserName(cellValue);
                        break;
                    }
                    case 3: {
                        raasTestAcc.setPhone(new BigInteger(cellValue));
                        break;
                    }
                    case 4: {
                        raasTestAcc.setEmail(cellValue);
                        break;
                    }
                    case 5: {
                        raasTestAcc.setLoginPassword(cellValue);
                        break;
                    }
                    case 6: {
                        raasTestAcc.setTransPassword(cellValue);
                        break;
                    }
                    case 7: {
                        raasTestAcc.setVerifyCode(cellValue);
                        break;
                    }
                    case 8: {
                        raasTestAcc.setClientId(cellValue);
                        break;
                    }
                    case 9: {   // 1: 使用, 0: 未使用
                        if (cellValue.toLowerCase().contains("yes")){
                            raasTestAcc.setFlagUsage(1);
                        } else if (cellValue.toLowerCase().contains("是")) {
                            raasTestAcc.setFlagUsage(1);
                        } else {
                            raasTestAcc.setFlagUsage(0);
                            LOGGER.info("是否使用，取值必须是'yes' 或者 '是'");
                        }
                        break;
                    }
                    case 10: {
                        if (cellValue.toLowerCase().contains("yes")) {
                            raasTestAcc.setIsHold(1);
                        } else if (cellValue.toLowerCase().contains("是")) {
                            raasTestAcc.setIsHold(1);
                        } else {
                            raasTestAcc.setIsHold(0);
                            LOGGER.info("是否持仓，取值必须是'yes' 或者 '是'");
                        }
                        break;
                    }case 11: {
                        raasTestAcc.setRaasUid(Long.valueOf(cellValue));
                        break;
                    }case 12: {
                        raasTestAcc.setAccountId(Long.valueOf(cellValue));
                        break;
                    }
                    case 13: {
                        raasTestAcc.setPortfolioId(Long.valueOf(cellValue));
                        break;
                    }
                    case 14: {
                        raasTestAcc.setUserRiskType(cellValue);
                        break;
                    }
                    case 15: {
                        raasTestAcc.setHoldings(cellValue);
                        break;
                    }
                    case 16: {
                        if (cellValue.toLowerCase().contains("yes")){
                            raasTestAcc.setHasAdvice(1);
                        } else if (cellValue.toLowerCase().contains("是")) {
                            raasTestAcc.setHasAdvice(1);
                        } else {
                            raasTestAcc.setHasAdvice(0);
                            LOGGER.info("有调仓建议，取值必须是'yes' 或者 '是'");
                        }
                        break;
                    }
                    case 17: {
                        raasTestAcc.setOperateRecord(cellValue);
                        break;
                    }
                    case 18: {
                        raasTestAcc.setRemark(cellValue);
                        break;
                    }
                    default:
                        throw new IllegalStateException("Unexpected value: " + col);
                }
            }
            raaSTestAccList.add(raasTestAcc);
        }
        LOGGER.info(String.valueOf(raaSTestAccList));
        return raaSTestAccList;
    }

    @Override
    public List<RaaSTestAcc> importAccExcel(RaaSAccountImportReq req, HttpServletRequest request) throws Exception {
        List<RaaSTestAcc> raaSTestAccList = new ArrayList<>();

        EasyExcel.read(req.getFile().getInputStream(), RaaSAccImport.class, new PageReadListener<RaaSAccImport>(dataList -> {
            for (RaaSAccImport raaSAccImport : dataList) {
                LOGGER.info("读取到一条数据{}", JSON.toJSONString(raaSAccImport));
                RaaSTestAcc raasTestAcc = new RaaSTestAcc();
                BeanUtils.copyProperties(raaSAccImport, raasTestAcc);

                String isUse = null != raaSAccImport.getFlagUsage()? raaSAccImport.getFlagUsage(): "";
                if (isUse.toLowerCase().contains("yes")){
                    raasTestAcc.setFlagUsage(1);
                } else if (isUse.toLowerCase().contains("是")) {
                    raasTestAcc.setFlagUsage(1);
                } else {
                    raasTestAcc.setFlagUsage(0);
                    LOGGER.info("是否使用，使用过，取值必须是'yes' 或者 '是'");
                }

                String isHold = null != raaSAccImport.getIsHold() ?raaSAccImport.getIsHold(): "";
                if (isHold.toLowerCase().contains("yes")){
                    raasTestAcc.setIsHold(1);
                } else if (isHold.toLowerCase().contains("是")) {
                    raasTestAcc.setIsHold(1);
                } else {
                    raasTestAcc.setIsHold(0);
                    LOGGER.info("是否有持仓，有持仓，取值必须是'yes' 或者 '是'");
                }

                String hasAdvice = null != raaSAccImport.getHasAdvice()? raaSAccImport.getHasAdvice(): "";
                if (hasAdvice.toLowerCase().contains("yes")){
                    raasTestAcc.setHasAdvice(1);
                } else if (hasAdvice.toLowerCase().contains("是")) {
                    raasTestAcc.setHasAdvice(1);
                } else {
                    raasTestAcc.setHasAdvice(0);
                    LOGGER.info("是否有调仓建议，有调仓建议，取值必须是'yes' 或者 '是'");
                }
                raasTestAcc.setCreator(req.getCreator());
                raaSTestAccList.add(raasTestAcc);
            }
            LOGGER.info("待导入数据库的列表{}", JSON.toJSONString(raaSTestAccList));
        })).sheet().doReadSync();
        return raaSTestAccList;
    }

    @Override
    public PageModule<RaaSAccListResp> getRaaSAccList(RaaSAccQueryReq raaSAccQueryReq) {
        List<RaaSAccListResp> resList = new ArrayList<>();

        QueryWrapper<RaaSTestAcc> wrapper = new QueryWrapper<>();
        Map<String, Object> params = new HashMap<>();
        params.put("flag_usage", raaSAccQueryReq.getFlagUsage());
        params.put("project", raaSAccQueryReq.getProject());
        params.put("is_delete", 0);
        wrapper.allEq(params, false);
        wrapper.like("holdings", raaSAccQueryReq.getHoldings());

        PageHelper.startPage(raaSAccQueryReq.getPageNum(), raaSAccQueryReq.getPageSize());
//         select * from raas_test_acc where id in (raaSAccQueryReq.getId()) [and ...any other condition];
        List<RaaSTestAcc> raaSTestAccList = raasTestAccMapper.selectList(wrapper);

        for (RaaSTestAcc raasTestAcc: raaSTestAccList) {
            RaaSAccListResp res = new RaaSAccListResp();
            BeanUtils.copyProperties(raasTestAcc, res);
            resList.add(res);
        }
        return PageModule.buildPage(resList, ((Page<RaaSTestAcc>) raaSTestAccList).getTotal());
    }

    @Override
    public void exportAccExcel(ArrayList<String> ids, HttpServletResponse response) throws IOException {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试账号", "UTF-8").replaceAll("\\+", "%20") +
                    LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy/MM/dd hh/mm/ss")) + ".xlsx";
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), RaaSAccExport.class).autoCloseStream(Boolean.FALSE).sheet("测试账号")
                    .doWrite(() -> dataList(ids));
            response.flushBuffer();
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

    private Collection<?> dataList(ArrayList<String> ids) {
        List<RaaSAccExport> resList = new ArrayList<>();
        QueryWrapper<RaaSTestAcc> wrapper = new QueryWrapper<>();
        Map<String, Object> params = new HashMap<>();
        params.put("is_delete", 0);
        wrapper.allEq(params, false);
        if(ids.size() > 0) {
            wrapper.in("id", ids);
        }

        List<RaaSTestAcc> raaSTestAccList = raasTestAccMapper.selectList(wrapper);

        for (RaaSTestAcc raasTestAcc: raaSTestAccList) {
            RaaSAccExport res = new RaaSAccExport();
            BeanUtils.copyProperties(raasTestAcc, res);
            String isHold = raasTestAcc.getIsHold() == 1? "是": "否";
            String isUse = raasTestAcc.getFlagUsage() == 1? "是": "否";
            String hasAdvice = raasTestAcc.getHasAdvice() == 1? "是": "否";
            res.setIsHold(isHold);
            res.setFlagUsage(isUse);
            res.setHasAdvice(hasAdvice);
            resList.add(res);
        }
        return resList;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int saveAccount(RaaSAccAddReq raaSAccAddReq) {
        RaaSTestAcc raasTestAcc = new RaaSTestAcc();
        BeanUtils.copyProperties(raaSAccAddReq, raasTestAcc);
        return raasTestAccMapper.insert(raasTestAcc);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int editAccount(RaaSAccEditReq raaSAccEditReqReq) {
        RaaSTestAcc raasTestAcc = new RaaSTestAcc();
        BeanUtils.copyProperties(raaSAccEditReqReq, raasTestAcc);
        return raasTestAccMapper.updateById(raasTestAcc);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean delAcc(Long id) {
        UpdateWrapper<RaaSTestAcc> updateWrapper = new UpdateWrapper<>();
        RaaSTestAcc  raaSTestAcc = new RaaSTestAcc();
        raaSTestAcc.setIsDelete(1);
        updateWrapper.eq("id", id);
        int affectRows = raasTestAccMapper.update(raaSTestAcc,updateWrapper);
        return affectRows > 0;
    }
}
